<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
	<xsl:output method="text" encoding="utf-8" indent="yes"/>
	<xsl:variable name="tableName" select="report/header/title/text()"/>
	
	<!-- =======================================================
		Template:		 MASTER Template.
		To do:		 Collect all templates to generate .
		Author:		 Khai Tran.
		Created Date:	 10 June 2011.
		Last Modified:	 13 June 2011.
	======================================================= -->
	<xsl:template match="/">
		<xsl:apply-templates select="report/section[@name='Columns']/table" mode="SP_INSERT"/>
		
		<xsl:text>&#xa;&#xa;</xsl:text>	
		<xsl:apply-templates select="report/section[@name='Columns']/table" mode="SP_SELECT"/>
		
		<xsl:text>&#xa;&#xa;</xsl:text>
		<xsl:apply-templates select="report/section[@name='Columns']/table" mode="SP_selectList"/>
		
		<xsl:text>&#xa;&#xa;</xsl:text>
		<xsl:apply-templates select="report/section[@name='Columns']/table" mode="SP_COUNT"/>
		
		<xsl:text>&#xa;&#xa;</xsl:text>
		<xsl:apply-templates select="report/section[@name='Columns']/table" mode="SP_DELETE"/>
		
		<xsl:text>&#xa;&#xa;</xsl:text>	
		<xsl:apply-templates select="report/section[@name='Columns']/table" mode="SP_UPDATE"/>
		<!---->
	</xsl:template>
	<!-- =============== END of MASTER Template ===================== -->
	
	<!-- =======================================================
		Template:		 GetDataTypesList and GetNumberList for rows.
		To do:		 .
		Author:		 Khai Tran.
		Created Date:	 13 June 2011.
		Last Modified:	 14 June 2011.
	======================================================= -->
	
	<xsl:template match="rows" mode="GetDataTypesList">
		<xsl:for-each select="row">
			<xsl:text>	</xsl:text>
			<xsl:value-of select="@c2"/>
			<xsl:if test="position()!=last()">
				<xsl:text>,</xsl:text>
			</xsl:if>
			<xsl:text>&#xa;</xsl:text>
		</xsl:for-each>
	</xsl:template>
	
	<xsl:template match="rows" mode="GetColumnsList">
		<xsl:for-each select="row">
			<xsl:text>	</xsl:text>
			<xsl:value-of select="@c1"/>
			<xsl:if test="position()!=last()">
				<xsl:text>,</xsl:text>
			</xsl:if>
			<xsl:text>&#xa;</xsl:text>
		</xsl:for-each>
	</xsl:template>
	
	<xsl:template match="rows" mode="GetNumberList">
		<xsl:for-each select="row">
			<xsl:text>$</xsl:text>
			<xsl:value-of select="@number"/>
			<xsl:if test="position()!=last()">
				<xsl:text>,</xsl:text>
			</xsl:if>
		</xsl:for-each>
	</xsl:template>
	<!-- =============== END of GetDataTypesList Template ===================== -->
	
	<!-- =======================================================
		Template:		 GetColumnsListInsert.
		To do:		 colect Datatypes alternate with Columnnames.
		Author:		 Dat Le.
		Created Date:	 14 June 2011.
		Last Modified:	 19 June 2011.
	======================================================= -->
	<xsl:template match="rows" mode="GetColumnsListInsert">
		<xsl:for-each select="row">
			<xsl:choose>
				<xsl:when test="@c4='Yes' and @c5 != ''">    <!--check sequence  -->	
				</xsl:when>
				<xsl:when test="@c5 != '' and contains(@c2 , 'time')">    <!--check default  -->	
				</xsl:when>
				<xsl:when test="@c1='deleted'"> 
				</xsl:when>
				<xsl:otherwise>
					<xsl:text></xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:if test="position()!=last()">
						<xsl:text>,</xsl:text>
					</xsl:if>
					<!--xsl:text>&#xa;</xsl:text-->
				</xsl:otherwise>
			</xsl:choose>
			
		</xsl:for-each>
	</xsl:template>
	
	<xsl:template match="rows" mode="GetColumnsListInsertParameters">
		<xsl:for-each select="row">
			<xsl:choose>
				<xsl:when test="@c4='Yes' and @c5 != ''">    <!--check sequence  -->	
				</xsl:when>
				<xsl:when test="@c5 != '' and contains(@c2 , 'time')">    <!--check default  -->	
				</xsl:when>
				<xsl:when test="@c1='deleted'"> 
				</xsl:when>
				<xsl:when test="@c1='updator'">    
					<xsl:text>_creator</xsl:text>
					<xsl:if test="position()!=last()">
						<xsl:text>,</xsl:text>
					</xsl:if>
				</xsl:when>
				<xsl:when test="@c1='deletion_time'">    
					<xsl:text>null</xsl:text>
					<xsl:if test="position()!=last()">
						<xsl:text>,</xsl:text>
					</xsl:if>
				</xsl:when>
				<xsl:otherwise>
					<xsl:text>_</xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:if test="position()!=last()">
						<xsl:text>,</xsl:text>
					</xsl:if>
					<!--xsl:text>&#xa;</xsl:text-->
				</xsl:otherwise>
			</xsl:choose>
			
		</xsl:for-each>
	</xsl:template>
	
	<xsl:template match="rows" mode="CheckTimestampInterval">
		<xsl:for-each select="row">
			<xsl:text>	</xsl:text>
			<xsl:if test="@number!=1">
				<xsl:text>&#xa;	AND </xsl:text>
			</xsl:if>
			<xsl:choose>
				<!--xsl:when test="@c2='timestamp' or @c2='timestamp without time zone'"-->
				<xsl:when test="contains(@c2, 'time')"> <!-- Khai changed here -->
					<xsl:text>(_</xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text>From</xsl:text>
					<xsl:text> is null OR </xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text> >= _</xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text>From)&#xa;	AND	</xsl:text>
					<xsl:text>(_</xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text>To</xsl:text>
					<xsl:text> is null OR </xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text> &lt;= _</xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text>To)</xsl:text>
				</xsl:when>
				<xsl:otherwise>
					<xsl:text>(_</xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text> is null OR </xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text> = _</xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text>)</xsl:text>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
	</xsl:template>
	<xsl:template match="rows" mode="GetDataTypesAndColumnsList2">
		<xsl:for-each select="row">
			<xsl:choose>
				<xsl:when test="contains(@c2, 'time')">
					<xsl:text>	_</xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text>From</xsl:text>
					<xsl:text>	</xsl:text>
					<xsl:value-of select="@c2"/>
					<xsl:text> DEFAULT null</xsl:text>	<!-- Khai added here! -->
					<xsl:text>,&#xa;</xsl:text>
					<xsl:text>	_</xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text>To</xsl:text>
					<xsl:text>	</xsl:text>
					<xsl:value-of select="@c2"/>					
				</xsl:when>				
				<xsl:otherwise>
					<xsl:text>	_</xsl:text>
					<xsl:value-of select="@c1"/>
					<xsl:text>	</xsl:text>
					<xsl:value-of select="@c2"/>					
				</xsl:otherwise>
			</xsl:choose>
			<xsl:text> DEFAULT null</xsl:text>	<!-- Khai added here! -->
			<xsl:if test="position()!=last()">
				<xsl:text>,</xsl:text>
			</xsl:if>
			<xsl:text>&#xa;</xsl:text>			
		</xsl:for-each>
	</xsl:template>
	<xsl:template match="rows" mode="GetDataTypesAndColumnsList">
		<xsl:for-each select="row">
			<xsl:text>	</xsl:text>
			<xsl:value-of select="@c1"/>
			<xsl:text>	</xsl:text>
			<xsl:value-of select="@c2"/>
			<xsl:if test="position()!=last()">
				<xsl:text>,</xsl:text>
			</xsl:if>
			<xsl:text>&#xa;</xsl:text>
		</xsl:for-each>
	</xsl:template>
	
	<xsl:template match="rows" mode="GetDataTypesAndColumnsListInsert">
		<xsl:for-each select="row[@c1!='created' and @c1!='updated'  and @c1!='updator' and @c1!='deleted' and @c1!='deletion_time' and (@c4='No' or @c5='')]">
			
			<xsl:text>	_</xsl:text>
			<xsl:value-of select="@c1"/>
			<xsl:text>	</xsl:text>
			<xsl:value-of select="@c2"/>
			<xsl:if test="@c3='No'">
				<xsl:text> DEFAULT null</xsl:text>
			</xsl:if>
			<xsl:if test="position()!=last()">
				<xsl:text>,</xsl:text>
			</xsl:if>
			<xsl:text>&#xa;</xsl:text>
		</xsl:for-each>
	</xsl:template>	
	<!-- =============== END of GetDataTypesAndColumnsListInsert Template ===================== -->
	
	<!-- =======================================================
		Template:		 INSERT.
		To do:		 Make insert stored procedure.
		Author:		 Dat Le.
		Created Date:	 10 June 2011.
		Last Modified:	 23 June 2011.
	======================================================= -->
	<xsl:template match="table" mode="SP_INSERT">
		<xsl:text>CREATE OR REPLACE FUNCTION sp_</xsl:text>
		<xsl:value-of select="$tableName"/>
		<xsl:text>_insert(&#xa;</xsl:text>	
			<xsl:apply-templates select="rows" mode="GetDataTypesAndColumnsListInsert"/>
		<xsl:text>)</xsl:text>
		<xsl:text>RETURNS int AS $$&#xa;</xsl:text>
		<xsl:text>BEGIN&#xa;</xsl:text>
		<xsl:text>	INSERT INTO </xsl:text>
		<xsl:value-of select="$tableName"/>
		<xsl:text>(</xsl:text>
			<xsl:apply-templates select="rows" mode="GetColumnsListInsert"/>
		<xsl:text>)&#xa;	VALUES(</xsl:text>
			<xsl:apply-templates select="rows" mode="GetColumnsListInsertParameters"/>	<!-- Dat changed here -->
		<xsl:text>);&#xa;</xsl:text>
		<xsl:text>RETURN </xsl:text>
		<xsl:choose>
			<xsl:when test="rows/row[@number='1']/@c5=''">
				<xsl:text>0;</xsl:text>
			</xsl:when>
			<xsl:otherwise>
				<xsl:text>currval('</xsl:text>
				<xsl:value-of select="$tableName"/>
				<xsl:text>_id_seq'::regclass);</xsl:text>
			</xsl:otherwise>
		</xsl:choose>
		<xsl:text>&#xa;END;&#xa;$$ LANGUAGE 'plpgsql';&#xa;</xsl:text>
	</xsl:template>
	<!-- ====================== END of INSERT =============================== -->
	
	<!-- =======================================================
		Template:		 SELECT.
		To do:		 Make SELECT stored procedure.
		Author:		 Dat Le.
		Created Date:	 10 June 2011.
		Last Modified:	 23 June 2011.
	======================================================= -->
	<xsl:template match="table" mode="SP_SELECT">
		<xsl:text>CREATE OR REPLACE FUNCTION sp_</xsl:text>
		<xsl:value-of select="$tableName"/>
		<xsl:text>_select(</xsl:text>
		<xsl:for-each select="rows/row[@c4='Yes']">	<!--to check PK -->
			<xsl:text>_</xsl:text>
			<xsl:value-of select="@c1"/>
			<xsl:text>	</xsl:text>
			<xsl:value-of select="@c2"/>	
			<xsl:if test="position()!=last()">
				<xsl:text> , </xsl:text>
			</xsl:if>
		</xsl:for-each>
		<xsl:text>)&#xa;RETURNS SETOF </xsl:text>	<!-- Khai changed here! -->
		<xsl:value-of select="$tableName"/>			<!-- Khai changed here! -->
		<xsl:text>&#xa;AS $$&#xa;BEGIN&#xa;	RETURN QUERY SELECT * FROM </xsl:text>
		<xsl:value-of select="$tableName"/>
		<xsl:text> WHERE </xsl:text>
		<xsl:for-each select="rows/row[@c4='Yes']">	<!-- to check PK -->
			<xsl:value-of select="@c1"/>	
			<xsl:text> = </xsl:text>
			<xsl:text>_</xsl:text>
			<xsl:value-of select="@c1"/>
			<xsl:if test="position()!=last()">
				<xsl:text> AND </xsl:text>
			</xsl:if>
		</xsl:for-each>
		<xsl:text>;&#xa;END;&#xa;$$ LANGUAGE 'plpgsql';&#xa;</xsl:text>
	</xsl:template>
	<!-- ====================== END of SELECT =============================== -->
	
	
	<!-- =======================================================
		Template:		Create_selectList_function.
		To do:		Create function sp_PurchaseOrder_selectList .
		Author:		Dat Le.
		Created Date:	13 June 2011.
		Last Modified:	23 June 2011.
	======================================================= -->
	<xsl:template match="table" mode="SP_selectList">
		<xsl:text>CREATE OR REPLACE FUNCTION sp_</xsl:text>
		<xsl:value-of select="$tableName"/>
		<xsl:text>_selectList (&#xa;</xsl:text>		
			<xsl:text>	_limit	integer,&#xa;	_offset integer,&#xa;</xsl:text>
			<xsl:apply-templates select="rows" mode="GetDataTypesAndColumnsList2"/>	
		<xsl:text>)&#xa;RETURNS SETOF </xsl:text>	<!-- Khai changed here! -->
		<xsl:value-of select="$tableName"/>			<!-- Khai changed here! -->
		<xsl:text>&#xa;AS $$&#xa;BEGIN&#xa;</xsl:text>
		<xsl:text>	IF _limit IS NULL THEN&#xa;		_limit = 2147483647;&#xa;	END IF;&#xa;</xsl:text>
		<xsl:text>	IF _offset IS NULL THEN&#xa;		_offset = 0;&#xa;	END IF;&#xa;</xsl:text>
		<xsl:text>RETURN QUERY SELECT * FROM </xsl:text>
		<xsl:value-of select="$tableName"/>
		<xsl:text> WHERE&#xa;	 </xsl:text>		
		<xsl:apply-templates select="rows" mode="CheckTimestampInterval"/>
		<xsl:text>	ORDER BY updated DESC </xsl:text>
		<!-- xsl:value-of select="rows/row[@c1='updated']/@c1"/--> <!-- Khai removed here! -->
		<xsl:text> LIMIT _limit OFFSET _offset;&#xa;</xsl:text>
		<xsl:text>END;&#xa;$$ LANGUAGE 'plpgsql';&#xa;</xsl:text>
	</xsl:template>
	<!-- ====================== END of selectList =============================== -->
	
	
	<!-- =======================================================
		Template:		Create_count_function.
		To do:		Create function sp_PurchaseOrder_count .
		Author:		Dat Le.
		Created Date:	13 June 2011.
		Last Modified:	14 June 2011.
	======================================================= -->	
	<xsl:template match="table" mode="SP_COUNT">
		<xsl:text>CREATE OR REPLACE FUNCTION sp_</xsl:text>
		<xsl:value-of select="$tableName"/>
		<xsl:text>_count (&#xa;</xsl:text>
			<xsl:apply-templates select="rows" mode="GetDataTypesAndColumnsList2"/>
		<xsl:text>)&#xa;RETURNS int AS $$&#xa;BEGIN&#xa;	RETURN COUNT(*) FROM </xsl:text>
		<xsl:value-of select="$tableName"/>
		<xsl:text> WHERE&#xa;	 </xsl:text>
		<xsl:apply-templates select="rows" mode="CheckTimestampInterval"/>
		<xsl:text>;&#xa;</xsl:text>
		<xsl:text>END;&#xa;$$ LANGUAGE plpgsql;&#xa;</xsl:text>
	</xsl:template>
	<!-- ====================== END of count =============================== -->
	
	
	<!-- =======================================================
		Template:		 DELETE.
		To do:		 Make delete stored procedure.
		Author:		 Hoang.
		Created Date:	 12 June 2011.
		Last Modified:	 14 June 2011.
	======================================================= -->
	<xsl:template match="table" mode="SP_DELETE">            <!--Dat changed here -->
		<xsl:text>CREATE OR REPLACE FUNCTION sp_</xsl:text>
		<xsl:value-of select="$tableName"/>
		<xsl:text>_delete(</xsl:text>
		
		<xsl:for-each select="rows/row[@c4='Yes']">
			<xsl:text>_</xsl:text>
			<xsl:value-of select="@c1"/>
			<xsl:text> </xsl:text>
			<xsl:value-of select="@c2"/>
				
			<xsl:if test="position()!=last()">
				<xsl:text>,	</xsl:text>	
			</xsl:if>
		</xsl:for-each>
		
		<xsl:text>)&#xa;</xsl:text>
		<xsl:text>	RETURNS INT as&#xa;</xsl:text>
		<xsl:text>$$</xsl:text>
		<xsl:text>&#xa;</xsl:text>	
		<xsl:text>BEGIN&#xa;</xsl:text>
		<xsl:text>		DELETE FROM </xsl:text>		
		<xsl:value-of select="$tableName"/>
		<xsl:text>&#xa;</xsl:text>
		<xsl:text> 		WHERE </xsl:text>
		<xsl:for-each select="rows/row[@c4='Yes']">	
			<xsl:text>"</xsl:text>		
			<xsl:value-of select="@c1"/>
			<xsl:text>"</xsl:text>
			<xsl:text> = _</xsl:text>
			<xsl:value-of select="@c1"/>
			<xsl:if test="position()!=last()">
				<xsl:text> AND </xsl:text>	
			</xsl:if>
		</xsl:for-each>	
		
		<xsl:text>;&#xa;		RETURN 0;&#xa;</xsl:text>
		<xsl:text>END;&#xa;</xsl:text>
		<xsl:text>$$ LANGUAGE 'plpgsql';&#xa;</xsl:text>
	</xsl:template>
	<!-- ====================== END of DELETE =============================== -->
	
	<!-- =======================================================
		Template:		 UPDATE.
		To do:		 Make update stored procedure.
		Author:		 Hoang.
		Created Date:	 12 June 2011.
		Last Modified:	 24 June 2011.
	======================================================= -->	
	<xsl:template match="table" mode="SP_UPDATE">            <!--Dat changed here -->
		<xsl:text>CREATE OR REPLACE FUNCTION sp_</xsl:text>
		<xsl:value-of select="$tableName"/>
		<xsl:text>_update &#xa;(</xsl:text>
			
		<!-- <xsl:value-of select="rows"/> -->
		<xsl:for-each select="rows/row">
			<xsl:if test="@c1 != 'created' and @c1 != 'updated' and @c1!='creator'">	<!--Dat changed here -->
				<xsl:text>	_</xsl:text>
				<xsl:value-of select="@c1"/>
				<xsl:text> </xsl:text>
				<xsl:value-of select="@c2"/>				
				<xsl:if test="position()!=last()">
					<xsl:text>,&#xa;</xsl:text>	
				</xsl:if>
			</xsl:if>
		</xsl:for-each>
		
		<xsl:text>  ) &#xa;RETURNS INT AS &#xa;</xsl:text>
		<xsl:text>$$</xsl:text>
		<xsl:text>&#xa;</xsl:text>
		<!-- <xsl:text>DECLARE item "</xsl:text><xsl:value-of select="$tableName" /><xsl:text>"%ROWTYPE;</xsl:text>
		<xsl:text>&#xa;</xsl:text> -->
		<xsl:text>BEGIN &#xa;</xsl:text>
		<!-- <xsl:text>	SELECT * INTO item FROM "</xsl:text><xsl:value-of select="$tableName" /><xsl:text>" WHERE </xsl:text><xsl:value-of select="$listPrimaryKey"/><xsl:text>;</xsl:text>
		<xsl:text>&#xa;</xsl:text> -->
		<xsl:text>	UPDATE </xsl:text>
		<xsl:value-of select="$tableName"/>
		<xsl:text> &#xa;</xsl:text>
		<xsl:text>	SET </xsl:text>
			<xsl:apply-templates select="rows/row[@c4!='Yes' and @c1!='created' and @c1!='creator']" mode="setListParam"/>
		<xsl:text>&#xa; 	WHERE </xsl:text>
		
		<xsl:value-of select="$listPrimaryKey"/>
		
		<xsl:text>;&#xa;	RETURN 0;&#xa;</xsl:text>
		<xsl:text>END; &#xa;</xsl:text>
		<xsl:text>$$ LANGUAGE plpgsql;&#xa;</xsl:text>
	</xsl:template>	
	<!-- ====================== END of UPDATE =============================== -->
	<!-- =======================================================
		Template:	Variables declaration , using in update stored procedure.
		To do:		 .
		Author:		 Hoang.
		Created Date:	 19 June 2011.
		Last Modified:	 20 June 2011.
	======================================================= -->	
	<xsl:variable name="listPrimaryKey">
		<xsl:for-each select="report/section[@name='Columns']/table/rows/row[@c4='Yes']">
			<xsl:value-of select="@c1"/>
			<xsl:text> = _</xsl:text>
			<xsl:value-of select="@c1"/>
			<xsl:if test="position()!=last()">
				<xsl:text> and </xsl:text>	
			</xsl:if>
		</xsl:for-each>
	</xsl:variable>
	
	<!-- ====================== END of TEMPLATE =============================== -->
	<!-- =======================================================
		Template:	UPDATE_SET_VALUE.
		To do:		 .
		Author:		 Hoang.
		Created Date:	 12 June 2011.
		Last Modified:	 02 July 2011.
	======================================================= -->	
	<xsl:template match="rows/row[@c4!='Yes']" mode="setListParam">
		<xsl:text>		</xsl:text>
		<xsl:value-of select="@c1"/>		
		<xsl:text> = </xsl:text>
		<xsl:choose> <!-- Khai changed this "Choose" -->
			<xsl:when test="@c1!='updated'">
				<xsl:text>_</xsl:text>
				<xsl:value-of select="@c1"/>				
			</xsl:when>
			<xsl:when test="@c1='updated'">
				<xsl:value-of select="@c5"/>
			</xsl:when>
		</xsl:choose>
		<xsl:if test="position()!=last()">
			<xsl:text>,&#xa;</xsl:text>
		</xsl:if>
	</xsl:template>
	<!-- ====================== END of TEMPLATE =============================== -->
</xsl:stylesheet>